﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Objects.DataClasses;
using System.Data.EntityClient;
using System.Data.Common;
using WDTAsg2.Admin;

namespace WDTAsg2.DbBizObject
{
    public class DownloadInfo
    {
        // Entity
        private HardwaresRUsDBEntities HardwaresRUsDBContext;

        /// <summary>
        /// Returns all downloads in a specified category
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static SqlDataReader GetDownloadsByCategory(int categoryId)
        {
            string conString = ConfigurationManager.ConnectionStrings["HardwaresRUsCS"].ConnectionString;

            // Stored proc
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand("getDownloadsByCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@CatId", categoryId);

            con.Open();
            SqlDataReader dtr = cmd.ExecuteReader();

            //con.Close();

            return dtr;
        }

        /// <summary>
        /// Returns a specific download
        /// </summary>
        /// <param name="downId"></param>
        /// <returns></returns>
        public static SqlDataReader GetDownloadById(int downId)
        {
            string conString = ConfigurationManager.ConnectionStrings["HardwaresRUsCS"].ConnectionString;

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand("getDownloadById", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@DownId", downId);

            con.Open();
            SqlDataReader dtr = cmd.ExecuteReader();

            //con.Close();

            return dtr;
        }


        /// <summary>
        /// Returns all downloads
        /// </summary>
        /// <returns></returns>
        public static SqlDataReader GetAllDownloads()
        {
            string conString = ConfigurationManager.ConnectionStrings["HardwaresRUsCS"].ConnectionString;

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand("getAllDownloads", con);
            cmd.CommandType = CommandType.StoredProcedure;


            con.Open();
            SqlDataReader dtr = cmd.ExecuteReader();

            //con.Close();

            return dtr;
        }


        /// <summary>
        /// Returns all approved downloads
        /// </summary>
        /// <returns></returns>
        public static SqlDataReader GetAllApprovedDownloads()
        {
            string conString = ConfigurationManager.ConnectionStrings["HardwaresRUsCS"].ConnectionString;

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand("getAllApprovedDownloads", con);
            cmd.CommandType = CommandType.StoredProcedure;


            con.Open();
            SqlDataReader dtr = cmd.ExecuteReader();

            //con.Close();

            return dtr;
        }


        /// <summary>
        /// Inserts a new entry into the downloads table
        /// </summary>
        /// <param name="title"></param>
        /// <param name="desc"></param>
        /// <param name="catid"></param>
        /// <param name="url"></param>
        public static void InsertDownload(string title, string desc, int catid, string url, string uploader)
        {
            string conString = ConfigurationManager.ConnectionStrings["HardwaresRUsCS"].ConnectionString;

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand("insertDownload", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Title", title);
            cmd.Parameters.AddWithValue("@Description", desc);
            cmd.Parameters.AddWithValue("@CategoryId", catid);
            cmd.Parameters.AddWithValue("@DownUrl", url);
            cmd.Parameters.AddWithValue("@Uploader", uploader);

            con.Open();
            cmd.ExecuteNonQuery();
            //con.Close();


        }


        /// <summary>
        /// Approve a download by ID
        /// </summary>
        /// <param name="downId"></param> The ID of the download
        /// <param name="approve"></param> True/False to approve or nor
        public static void ApproveDownloadById(int downId, bool approve)
        {

            // grab entity
            HardwaresRUsDBEntities nwd = new HardwaresRUsDBEntities();
            EntityConnection ec = (EntityConnection)nwd.Connection;

            // stored procedure
            DbConnection dc = ec.StoreConnection;
            DbCommand command = dc.CreateCommand();
            command.CommandText = "approveDownloadById";
            command.CommandType = CommandType.StoredProcedure;

            // Paremeetres
            SqlParameter downloadID = new SqlParameter("@DownId", SqlDbType.Int, 1);
            downloadID.Direction = ParameterDirection.Input;
            downloadID.Value = downId;
            command.Parameters.Add(downloadID);

            SqlParameter isApproved = new SqlParameter("@Approved", SqlDbType.Bit, 1);
            isApproved.Direction = ParameterDirection.Input;

            // Is approved or not
            if (approve == true)
                isApproved.Value = true;
            else
                isApproved.Value = false;

            command.Parameters.Add(isApproved);

            // Check if connection is already opened
            bool openConnection = command.Connection.State ==
                ConnectionState.Closed;

            // open it if its not
            if (openConnection)
                command.Connection.Open();

            // run the command
            var result = command.ExecuteReader();

        }

    }
}